﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AuctionDataLayer;
using Auction_Intranet.Helpers;

namespace Auction_Intranet.Models
{
    public class DataRepository
    {
        AuctionDBDataContext m_db = new AuctionDBDataContext();

        public IEnumerable<Log> getErrorLog()
        {
            var result = from item in m_db.Logs
                         orderby item.Date descending
                         select item;
            return result;
        }

        #region Amenities

        public IEnumerable<Amenity> GetAllAmenities()
        {
            return from item in m_db.Amenities where item.Active == true select item;            
        }

        public void addAmenity(Amenity model)
        {
            if (model != null)
            {
                m_db.Amenities.InsertOnSubmit(model);
                m_db.SubmitChanges();
            }
        }
        public Amenity GetAmenitieById(int id)
        {
            var result = (from item in m_db.Amenities
                          where item.Id == id
                          select item).SingleOrDefault();
            return result;
        }

        public void DeleteAmenitie(Amenity model)
        {
            var result = GetAmenitieById(model.Id);
            if (result != null)
            {
                result.Active = false;
                m_db.SubmitChanges();
            }            
        }

        public void updateAmenity(Amenity model, string name)
        {
            var result = GetAmenitieById(model.Id);
            if (result != null)
            {
                result.Name = name;
                m_db.SubmitChanges();                
            }
        }

        #endregion

        #region Rooms

        public void DeleteRoomPicture(RoomPicture model)
        {
            if (model != null)
            {
                m_db.RoomPictures.DeleteOnSubmit(model);
                m_db.SubmitChanges();
            }
        }

        public RoomPicture GetRoomPIctureById(int picId, int roomId)
        {
            var result = (from item in m_db.RoomPictures
                          where item.Id == picId && item.RoomId == roomId
                          select item).SingleOrDefault();
            return result;
        }

        public void DeleteRoomsAmenities(IEnumerable<RoomAmenity> models)
        {
            if (models != null)
            {
                m_db.RoomAmenities.DeleteAllOnSubmit(models);
                m_db.SubmitChanges();
            }
        }

        public Room getRoomById(int id)
        {
            var result = (from item in m_db.Rooms
                          where item.Id == id
                          select item).SingleOrDefault();
            return result;
        }

        public void AddRoomAmenities(List<RoomAmenity> roomAmenities)
        {
            if(roomAmenities != null)
            {
                m_db.RoomAmenities.InsertAllOnSubmit(roomAmenities);
                m_db.SubmitChanges();
            }
        }

        public IEnumerable<RoomPicture> GetRoomPicturesByRoomId(int id)
        {
            var result = from item in m_db.RoomPictures
                         where item.RoomId == id
                         select item;
            return result;
        }

        public IEnumerable<RoomAmenity> GetAmenitiesByRoomBy(int id)
        {
            var result = from item in m_db.RoomAmenities
                         where item.RoomId == id
                         select item;
            return result;
        }

        public void AddNewRoomPicture(RoomPicture model)
        {
            m_db.RoomPictures.InsertOnSubmit(model);
            m_db.SubmitChanges();
        }

        public void updateRoom(Room model)
        {
            var result = getRoomById(model.Id);
            if (result != null)
            {
                result.Name = model.Name;
                result.Location = model.Location;
                result.Capacity = model.Capacity;
                result.Size = model.Size;
                result.Deleted = model.Deleted;
                m_db.SubmitChanges();
            }
        }

        public void addNewRoom(Room model)
        {
            m_db.Rooms.InsertOnSubmit(model);
            m_db.SubmitChanges();
        }

        public IEnumerable<Room> GetAllRooms()
        {
            var result = from item in m_db.Rooms
                         where item.Deleted == false
                         select item;
            return result;
        }

        public void UpdateBidAnyRoomId(int id)
        {
            var result = (from item in m_db.Bids
                          where item.Id == id
                          select item).SingleOrDefault();
            if (result != null)
            {
                result.AnyRoomId = result.Id;
                m_db.SubmitChanges();
            }

        }

        #endregion

        #region Group

        public void UpdateGroupBalance(int amount, int id)
        {
            var result = GetGroupById(id);
            if (result != null)
            {
                result.Balance = amount;
                m_db.SubmitChanges();
            }
        }

        public void AddMemberToSingleGroup(UserInGroup userInGroup)
        {
            if (userInGroup != null)
            {
                m_db.UserInGroups.InsertOnSubmit(userInGroup);
                m_db.SubmitChanges();
            }
        }

        public void CreateNewSingleGroup (Group group)
        {
            if(group != null)
            {
                m_db.Groups.InsertOnSubmit(group);
                m_db.SubmitChanges();
            }        
        }

        public Group GetGroupById(int id)
        {
            var result = (from item in m_db.Groups
                          where item.Id == id
                          select item).SingleOrDefault();
            return result;
        }
        public List<UserInGroup> GetMembersOfGroupById(int id)
        {
            var result = (from item in m_db.UserInGroups
                         where item.GroupId == id && item.ActiveMember == true
                         select item).ToList();
            return result;
        }

        public List<UserInGroup> GetUserInGroupByUserID(int id)
        {
            var result = (from item in m_db.UserInGroups
                          where item.UserId == id && item.ActiveMember == true
                          select item).ToList();
            return result;
        }

        public IEnumerable<UserInGroup> GetUsersInGroupById(int id)
        {
            var result =  from item in m_db.UserInGroups
                          where item.GroupId == id && item.ActiveMember == true
                          select item;
            return result;
        }

        public IEnumerable<UserInGroupBid> GetUserInGroupBidById(IEnumerable<UserInGroup> userInGroupList, int bidId)
        {            
            var result = from item in m_db.UserInGroupBids
                         where item.BidId == bidId && userInGroupList.Where(x => x.Id == item.UserInGroupId).Any()
                         select item;
            return result;
        }

        public IEnumerable<UserInGroupBid> GetUserInGroupBidByIdOnly(int bidId)
        {
            var result = from item in m_db.UserInGroupBids
                         where item.BidId == bidId
                         select item;
            return result;
        }


        public UserInGroup GetUserInGroupById(int userId, int groupId)
        {
            var result = (from item in m_db.UserInGroups
                          where item.UserId == userId && item.GroupId == groupId && item.ActiveMember == true
                          select item).SingleOrDefault();
            return result;
        }

        public IEnumerable<UserInGroupBid> GetAllUserInGroupBidsForUserInGroup(int userInGroupId, int groupId)
        {
            var result = from item in m_db.UserInGroupBids
                          where item.UserInGroupId == userInGroupId && item.UserInGroup.GroupId == groupId
                          select item;
            return result;
        }
        public IEnumerable<UserInGroupBid> GetUserInGroupBidsForActiveUserInGroup(int userId)
        {
            //Returns all UserInGroupsBid for an active member and a bid that is pending
            var result = from item in m_db.UserInGroupBids
                         where item.UserInGroup.UserId == userId && item.UserInGroup.ActiveMember == true && item.Bid.State.Id == 1
                         select item;
            return result;
        }
        public IEnumerable<UserInGroupBid> GetUserInGroupBidsForInactiveUserInGroup(int userId)
        {
            //Returns all UserInGroupsBid for an inactive member and a bid that is pending
            var result = from item in m_db.UserInGroupBids
                         where item.UserInGroup.UserId == userId && item.UserInGroup.ActiveMember == false && item.Bid.State.Id == 1
                         select item;
            return result;
        }
        public IEnumerable<UserInGroupBid> GetAcceptedUserInGroupBidsForUserInGroup(int userId)
        {
            //Returns all accepted UserInGroupsBid for a member
            var result = from item in m_db.UserInGroupBids
                         where item.UserInGroup.UserId == userId && item.Bid.State.Id == 2
                         select item;
            return result;
        }
        public UserInGroupBid GetUserInGroupBidById(int userInGroupId, int bidId)
        {
            var result = (from item in m_db.UserInGroupBids
                          where item.UserInGroupId == userInGroupId && item.BidId == bidId
                          select item).SingleOrDefault();
            return result;
        }

        public void AddGroup(Group model)
        {
            m_db.Groups.InsertOnSubmit(model);
            m_db.SubmitChanges();
        }
        public void CreateNewGroup(int id, CreateGroupModel model)
        {
            var user = GetUserById(id);
            if (user != null)
            {
                // create group
                Group create = new Group();
                create.Name = model.name;
                create.Balance = model.addedCredit;
                create.Created = DateTime.Now;
                AddGroup(create);

                //Update userbalance 
                user.Balance -= model.addedCredit;

                //create userInGroup
                AddUserInGroup(user.Id, create.Id, model.addedCredit);

                //create invitations
                ////Get groupMembers need to know hom many and they need to be validated
                //foreach (var item in members)
                //{
                //    // temporarily if the user is already in the system 
                //    // ToDO handle if the user does not exist and maby change 
                //    //the invitations table where the username is user instead of the userid
                //    // maby the invitation table should have state so if you have acceped or declined it will just not
                //    //appear and when coould be marked pending

                //    var member = GetUserByUsername(item);
                //    if(member != null)
                //    {
                //        AddInvitation(member.Id, create.Id);
                //    }
                   
                //}
                m_db.SubmitChanges();

            }
        }
        public void AddUserInGroup(int userId, int groupId, int creditAdded)
        {
            UserInGroup member = new UserInGroup();
            member.UserId = userId;
            member.GroupId = groupId;
            member.CreditAdded = creditAdded;
            member.CreditAddedSpent = 0;
            member.ActiveMember = true;
            m_db.UserInGroups.InsertOnSubmit(member);
            m_db.SubmitChanges();
 
        }
  
        public void AddCreditToUserInGroup(int amount, int userId, int groupId)
        {
            UserInGroup userInGroup = GetUserInGroupById(userId, groupId);
            var user = GetUserById(userId);
            //var group = GetGroupById(groupId);
            if (userInGroup != null)// && user != null)// && group != null)
            {
                userInGroup.CreditAdded = userInGroup.CreditAdded + amount;
                //user.Balance = user.Balance - model.creditAdded;
                //group.Balance = group.Balance + model.creditAdded;
                m_db.SubmitChanges();
            }
        }
        public List<string> GetAllGroupNames()
        {
            var result = (from item in m_db.Groups
                          select item.Name).ToList();
            return result;
        }
        #endregion

        #region Bids

        public Bid getBidById(int id)
        {
            var result = (from item in m_db.Bids
                          where item.Id == id
                          select item).SingleOrDefault();
            return result;
        }

        public IEnumerable<Bid> getBidByIdForAnyRoom(int id)
        { 
            var result = from item in m_db.Bids
                         where item.AnyRoomId == id
                         select item;
            return result;
        }

        public IEnumerable<Bid> GetCurrentDayResult()
        {
            var result = from item in m_db.Bids
                         where item.State.Name == "Accepted"
                         select item;
            return result;
        }

        public IEnumerable<Bid> GetAllPendingBidsByDate(DateTime date)
        {
            var result = from item in m_db.Bids
                         where item.Date.Day == date.Day &&
                               item.Date.Month == date.Month &&
                               item.Date.Year == date.Year &&
                               item.StateId == 1
                         select item;
            return result;
        }

        public IEnumerable<Bid> GetAllBidsByDate(DateTime date)
        {
            var result = from item in m_db.Bids
                         where item.Date.Day == date.Day &&
                               item.Date.Month == date.Month &&
                               item.Date.Year == date.Year
                         select item;
            return result;
        }

        public IEnumerable<Bid> GetAllPendingBidsByWeek(int week)
        {
            var result = from item in m_db.Bids
                         where item.Date.WeekNumber() == week &&
                               item.StateId == 1
                         select item;
            return result;
        }
        
        public IEnumerable<Bid> GetBidResultByDate(DateTime date)
        {
            var result = from item in m_db.Bids
                         where item.Date.Day == date.Day &&
                               item.Date.Month == date.Month &&
                               item.Date.Year == date.Year &&
                               item.StateId == 2
                         select item;
            return result;
        }

        public void AddToUserInGroupByBid(UserInGroupBid model)
        {
            if (model != null)
            {
                m_db.UserInGroupBids.InsertOnSubmit(model);
                m_db.SubmitChanges();
            }
        }

        public void UpdateUserInGroup(List<ToPayModel> list, int groupId, int BidId)
        {
            var group = GetGroupById(groupId);
            if (list != null && group != null)
            {
                foreach (var item in list)
                {
                    var userToUpdate = GetUserInGroupById(item.userId, group.Id);
                    userToUpdate.CreditAddedSpent += item.amountToPay;

                    UserInGroupBid model = new UserInGroupBid();
                    model.UserInGroupId = userToUpdate.Id;
                    model.BidId = BidId;
                    model.GroupBalance = group.Balance;
                    model.MembersCredit = userToUpdate.CreditAdded - userToUpdate.CreditAddedSpent;
                    model.MembersCreditPaid = item.amountToPay;
                    AddToUserInGroupByBid(model);

                    m_db.SubmitChanges();
                }
                //Update group info
                group.Balance -= list.Select(x => x.amountToPay).Sum();
                m_db.SubmitChanges();
            }
        }

        public IEnumerable<Bid> GetUserBidByUserId(int id)
        {
            var result = m_db.Bids.Where(x => ((m_db.UserInGroups.Where(z => z.UserId == id)).Select(y => y.GroupId)).Contains(x.GroupId)).Where(z => z.Id == z.AnyRoomId);
            return result;
        }

        public void CreateNewBid(Bid model)
        {
            if (model != null)
            {
                m_db.Bids.InsertOnSubmit(model);
                m_db.SubmitChanges();
            }
        }

        public void deleteUserInGroupBidList(IEnumerable<UserInGroupBid> models)
        {
            if (models != null)
            {
                m_db.UserInGroupBids.DeleteAllOnSubmit(models);
                m_db.SubmitChanges();
            }
        }

        public void deleteBid(Bid model)
        {
            var result = getBidById(model.Id);
            if(result != null)
            {
                m_db.Bids.DeleteOnSubmit(result);
                m_db.SubmitChanges();
            }
        }

        public void deleteAllBid(IEnumerable<Bid> model)
        {
            if (model != null)
            {
                m_db.Bids.DeleteAllOnSubmit(model);
                m_db.SubmitChanges();
            }
        }

        public void deleteUserInGroupBid(UserInGroupBid model)
        {
            var result = GetUserInGroupBidById(model.UserInGroupId, model.BidId);
            if (result != null)
            {
                m_db.UserInGroupBids.DeleteOnSubmit(result);
                m_db.SubmitChanges();
            }
        }

        public void UpdateUserInGroupBalance(UserInGroup model)
        {
            var result = GetUserInGroupById(model.UserId, model.GroupId);
            if (result != null)
            {
                result.CreditAddedSpent = model.CreditAddedSpent;
                m_db.SubmitChanges();
            }
        }
        public IEnumerable<Bid> GetBidsByGroupId(int id)
        {
            var result = from item in m_db.Bids
                         where item.GroupId == id && item.Id == item.AnyRoomId
                         select item;
            return result;
        }
        public List<Bid> GetPendingBidsByGroupId(int id)
        {
            var result = (from item in m_db.Bids
                          where item.GroupId == id && item.State.Name == "Pending"
                          select item).ToList();
            return result;
        }
        public List<Bid> GetAcceptedBidsByGroupId(int id)
        {
            var result = (from item in m_db.Bids
                          where item.GroupId == id && item.State.Name == "Accepted"
                          select item).ToList();
            return result;
        }
        public List<Bid> GetDeclinedBidsByGroupId(int id)
        {
            var result = (from item in m_db.Bids
                          where item.GroupId == id && item.State.Name == "Declined"
                          select item).ToList();
            return result;
        }
        public List<Bid> GetCanceledBidsByGroupId(int id)
        {
            var result = (from item in m_db.Bids
                          where item.GroupId == id && item.State.Name == "Canceled"
                          select item).ToList();
            return result;
        }
       #endregion

        #region Invitations and notificatinos

        public void UpdateInvitationStatus(int status, int id)
        {
            Invitation invitation = GetInvitaionById(id);
            if (invitation != null)
            {
                invitation.StatusId = status;
                m_db.SubmitChanges();
            }
        }
        public void AddInvitation(int userId, int groupId)
        {
            Invitation invitation = new Invitation();
            invitation.UserId = userId;
            invitation.GroupId = groupId;
            invitation.StatusId = 1;
            m_db.Invitations.InsertOnSubmit(invitation);
            m_db.SubmitChanges();
        }
        public Invitation GetInvitaionById(int id)
        {
            var result = (from item in m_db.Invitations
                         where item.Id == id
                         select item).SingleOrDefault();
            return result;
        }
        public void RemoveInvitation(Invitation model)
        {
            m_db.Invitations.DeleteOnSubmit(model);
            m_db.SubmitChanges();
        }
        public IEnumerable<Invitation> GetUserInvitationsByUserId(int id)
        { 
            var result = from item in m_db.Invitations
                            where item.UserId == id && item.State.Name == "Pending"
                            select item;
            return result;
        }
        public List<Invitation> GetPendingInvitationsByGroupId(int id)
        {
            var result = (from item in m_db.Invitations
                         where item.GroupId == id && item.State.Name == "Pending"
                         select item).ToList();
            return result;
        }

        public IEnumerable<Notification> GetUserNotificationsByUserId(int id)
        {
            var result = from item in m_db.Notifications
                         where item.UserId == id
                         select item;
            return result;        
        }

        public Notification GetNotificationById(int id)
        {
            var result = (from item in m_db.Notifications
                         where item.Id == id
                         select item).SingleOrDefault();
            return result;
        }

        public void DeleteNotification(Notification model)
        {
            m_db.Notifications.DeleteOnSubmit(model);
            m_db.SubmitChanges();
        }

        #endregion

        #region Times

        public IEnumerable<AvailableBidTime> GetAvailableBidTimes()
        {
            var result = from item in m_db.AvailableBidTimes
                         select item;
            return result;
        }

        #endregion

        #region State
        public State GetStateByName(string state)
        {
            var result = (from item in m_db.States
                         where item.Name == state
                         select item).SingleOrDefault();
            return result;
        }
        #endregion

        #region Users

        public IEnumerable<HRStudents_> GetAllRUUsernamesForQuery(string query)
        {
            var result = (from item in m_db.HRStudents_s
                          where item.sName.Contains(query) ||
                                item.sUser.Contains(query)
                          select item).GroupBy(x => x.sUser).Select(y => y.First());
            return result;
        }

        public IEnumerable<User> GetAllUsers()
        {
            var result = from item in m_db.Users
                         select item;
            return result;
        }

        public void UpdateUserInGroupStatus(bool active, int userId, int groupId)
        {
            UserInGroup userInGroup = GetUserInGroupById(userId, groupId);
            if (userInGroup != null)
            {
                userInGroup.ActiveMember = active;
            }
        }

        public void UpdateUserInGroupSpent(int amount, int userId, int groupId)
        {
            var result = GetUserInGroupById(userId, groupId);
            if (result != null)
            {
                result.CreditAddedSpent += amount;
                m_db.SubmitChanges();
            }
        }

        public void UpdateUserInGroupCreditAdded(int amount, int userId, int groupId)
        {
            var result = GetUserInGroupById(userId, groupId);
            if (result != null)
            {
                result.CreditAdded = amount;
                m_db.SubmitChanges();
            }
        }

        public void UpdateUserInGroupRefund(int amount, int userId, int groupId)
        {
            var result = GetUserInGroupById(userId, groupId);
            if (result != null)
            {
                result.CreditAddedSpent -= amount;
                m_db.SubmitChanges();
            }
        }

        public void UpdateUserBalance(int amount, int userId)
        {
            var result = GetUserById(userId);
            if (result != null)
            {
                result.Balance = amount;
                m_db.SubmitChanges();
            }
        }

        public void UpdadeUserSingleGroupId(int userId, int groupId)
        {
            var result = GetUserById(userId);
            if (result != null)
            {
                result.MyGroupId = groupId;
                m_db.SubmitChanges();
            }
        }

        public IEnumerable<HRStudents_> GetAllRUUsernames(string username, string query)
        {
            var result = m_db.HRStudents_s.Where(x => x.sUser != username && (x.sUser.Contains(query) || x.sName.Contains(query))).GroupBy(x => x.sUser).Select(y => y.First());
            return result;

        }

        public IEnumerable<HRStudents_> GetAllUsernamesExceptGroupMembers(List<string> groupMembers, string query)
        {
            var result = m_db.HRStudents_s.Where(x => !groupMembers.Contains(x.sUser) && (x.sUser.Contains(query) || x.sName.Contains(query))).GroupBy(x => x.sUser).Select(y => y.First());
            return result;
        }

        public IEnumerable<HRStudents_> GetAllRUUsernamesFiltered(string username, string query)
        {
            var result = m_db.HRStudents_s.Where(x => x.sUser != username && (x.sUser.Contains(query) || x.sName.Contains(query)));
            var result2 = m_db.HRStudents_s.Where(x => x.sUser != username && (x.sUser.StartsWith(query) || x.sName.StartsWith(query))).ToList();
            result2.AddRange(result);
            return result2.GroupBy(x => x.sUser).Select(y => y.First());
        }
        public IEnumerable<HRStudents_> GetAllUsernamesExceptGroupMembersFiltered(List<string> groupMembers, string query)
        {
            var result = m_db.HRStudents_s.Where(x => !groupMembers.Contains(x.sUser) && (x.sUser.Contains(query) || x.sName.Contains(query)));
            var result2 = m_db.HRStudents_s.Where(x => !groupMembers.Contains(x.sUser) && (x.sUser.StartsWith(query) || x.sName.StartsWith(query))).ToList();
            result2.AddRange(result);

            return result2.GroupBy(x => x.sUser).Select(y => y.First());
        }

        public IEnumerable<Group> GetGroupsByUserId(int id)
        {
            var groupIdForUser = from item in m_db.UserInGroups
                                 where item.UserId == id && item.ActiveMember == true && item.GroupId != item.User.MyGroupId
                                 select item.GroupId;

            var userGroups = from item in m_db.Groups
                             where groupIdForUser.Contains(item.Id)
                             select item;
            return userGroups;

        }

        public User GetUserByUsername(string username)
        {
            var result = (from item in m_db.Users
                          where item.Username == username
                          select item).SingleOrDefault();
            return result;
        }

        public User GetUserById(int id)
        {
            var result = (from item in m_db.Users
                          where item.Id == id
                          select item).SingleOrDefault();
            return result;
        }

        public void AddNewUserForAuction(User model)
        {
            m_db.Users.InsertOnSubmit(model);
            m_db.SubmitChanges();
        }

        public User getOrCreateRUUserByUserName(string username)
        {
            var ruUser = (from item in m_db.HRStudents_s
                          where item.sUser == username
                          select item).FirstOrDefault();
            string x = ConfigurationManager.AppSettings["StartCredit"];
            int startCredit = Convert.ToInt32(x);
            if (ruUser != null)
            {
                User result = new User();
                result.Username = username;
                result.Name = ruUser.sName;
                result.Email = ruUser.sEmail;
                result.Department = ruUser.sDepartment;
                result.Study = ruUser.sMajor;
                result.Balance = startCredit;
                return result;
            }
            else
            {
                User result = new User();
                result.Username = username;
                result.Balance = startCredit;
                return result;
            }
        }

        public HRStudents_ getRUUserByUserName(string username)
        {
            var result = (from item in m_db.HRStudents_s
                          where item.sUser == username
                          select item).FirstOrDefault();
            return result;
        }

        public IEnumerable<HRStudents_> getAllRUUserse(string username)
        {
            var result =  from item in m_db.HRStudents_s
                          where item.sUser == username
                          select item;
            return result;
        }

        public IEnumerable<HRStudents_> GetAllRUUsers()
        {
            var result = from item in m_db.HRStudents_s
                         select item;
            return result;
        }

        #endregion

        public List<UserInGroup> GetMembersOfGroupByIdAll(int id)
        {
            var result = (from item in m_db.UserInGroups
                          where item.GroupId == id
                          select item).ToList();
            return result;
        }
    }
}
